﻿create procedure [store].[kvHarden]
as 
begin 
	declare @Now datetime2 = getutcdate()
	declare @kv as table
	(
		[Key] varchar(255) COLLATE Latin1_General_100_BIN2 not null, 
		[KeyId] bigint not null,
		[Version] int not null,
		[Harden] bit not null
	)

	-- this will do a full in-memory table scan. Because of the index overhead due to expiry churn,
	-- scanning is the most efficient path for this purpose, since this process will be run offline.
	insert into @kv 
	(
		[Key], 
		[KeyId], 
		[Version],
		[Harden]
	) 
	select 
		[Key], 
		[KeyId], 
		[Version],
		[Harden]
	from mem.kv 
	where Expiry < @Now

	declare @KeyId bigint
	declare @Key varchar(255)
	declare @Harden bit

	select top 1 @KeyId = KeyId, @Key = [Key], @Harden = Harden from @kv
	while @@rowcount > 0
	begin
		if @Harden = 1
		begin
			merge into store.[kvPersisted] dest using @kv src 
				on src.[Key] = dest.[Key]
				when matched then update 
				set
					dest.[KeyId] = src.[KeyId], 
					dest.[Version] = src.[Version],
					dest.[PersistedTime] = GETUTCDATE()
				when not matched then insert
				values
					(
						src.[Key],
						src.[KeyId],
						src.[Version],
						GETUTCDATE()
					);

			delete from mem.kvValues
			output deleted.[KeyId], deleted.[SortOrder], deleted.[Part] into store.kvPersistedValues 
			where [KeyId] = @KeyId
		end
		else 
		begin
			delete from mem.kvValues where KeyId = @KeyId
		end
		
		delete from mem.kv where [Key] = @Key
		delete from @kv where [Key] = @Key
		select top 1 @KeyId = KeyId, @Key = [Key] from @kv
	end
end